﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;

namespace Customs.Components.SLXNK
{
	/// <summary>
	/// Tờ khai phi mậu dịch.
	/// </summary>
	public class ToKhaiPhiMauDich
	{
		public static DataSet Search(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy, DateTime tuNgay, DateTime denNgay, string maDonVi, string nuocXuatKhau, string nuocNhapKhau, string maCuaKhau, decimal tgkb, byte tgkb_Compare)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return Search(connectionString, soToKhai, maLoaiHinh, maHaiQuan, namDangKy, tuNgay, denNgay, maDonVi, nuocXuatKhau, nuocNhapKhau, maCuaKhau, tgkb, tgkb_Compare);			
		}

        public static DataSet Search(int soToKhai, string maLoaiHinh, string maHaiQuanMoToKhai, DateTime tuNgay, DateTime denNgay)
        {
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT TK.SoTK AS SoToKhai, ");
            query.Append("TK.Ngay_DK AS NgayDangKy, ");
            query.Append("TK.Ma_LH AS MaLoaiHinh, ");
            query.Append("LH.Ten_LH AS TenLoaiHinh, ");
            query.Append("TK.Ma_HQ AS MaHaiQuan, ");
            query.Append("HQ.Ten_HQ AS TenHaiQuan, ");
            query.Append("TONGTGKB, ");
            query.Append("(CONVERT(varchar(10), TK.SoTK) + '-' + TK.Ma_LH + '-' + RTRIM(TK.MA_HQ) + '-' + CONVERT(varchar(10), TK.NamDK)) AS Khoa ");
            query.Append("FROM ");
            query.Append("DToKhaiPMD TK ");
            query.Append("INNER JOIN SLHINHPMD LH ON TK.MA_LH = LH.MA_LH ");
            query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");

            query.Append("WHERE ");
            if (soToKhai > 0)
                query.Append(string.Format("TK.SoTK = {0} AND ", soToKhai));

            // Loai hinh.
            if (maLoaiHinh != "   ")
                query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", maLoaiHinh.PadRight(5)));

            // Hai quan mo to khai.
            if (maHaiQuanMoToKhai != "   ")
                query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", maHaiQuanMoToKhai.PadRight(6)));

            // Tu ngay -> den ngay.
            if (tuNgay.Year > 1900 && denNgay.Year > 1900)
                query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) ");


            query.Append("ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
            parameters[0].Value = tuNgay;

            parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
            parameters[1].Value = denNgay;

            try
            {
                string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuanMoToKhai);
                DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    row["TenLoaiHinh"] = FontConverter.TCVN2Unicode(row["TenLoaiHinh"].ToString());
                    row["TenHaiQuan"] = FontConverter.TCVN2Unicode(row["TenHaiQuan"].ToString());
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + ": " + query);
            }
        }


		public static DataSet Search(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuanMoToKhai, int namDangKy, DateTime tuNgay, DateTime denNgay, string maDonVi, string nuocXuatKhau, string nuocNhapKhau, string maCuaKhau, decimal tgkb, byte tgkb_Compare)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT TK.SoTK AS SoToKhai, ");
			query.Append("TK.Ngay_DK AS NgayDangKy, ");
			query.Append("TK.Ma_LH AS MaLoaiHinh, ");
			query.Append("LH.Ten_LH AS TenLoaiHinh, ");
			query.Append("TK.Ma_HQ AS MaHaiQuan, ");
			query.Append("HQ.Ten_HQ AS TenHaiQuan, ");
			query.Append("TONGTGKB, ");			
			query.Append("(CONVERT(varchar(10), TK.SoTK) + '-' + TK.Ma_LH + '-' + RTRIM(TK.MA_HQ) + '-' + CONVERT(varchar(10), TK.NamDK)) AS Khoa ");
			query.Append("FROM ");
			query.Append("DToKhaiPMD TK ");
			query.Append("INNER JOIN SLHINHPMD LH ON TK.MA_LH = LH.MA_LH ");
			query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");			

			query.Append("WHERE ");
			if (soToKhai > 0)
				query.Append(string.Format("SoTK = {0} AND ", soToKhai));

			// Loai hinh.
			if (maLoaiHinh != "   ")
				query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", maLoaiHinh));

			// Hai quan mo to khai.
			if (maHaiQuanMoToKhai != "   ")
				query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", maHaiQuanMoToKhai));

			// Nam dang ky.
			if (namDangKy > 1900)
				query.Append(string.Format("TK.NamDK = '{0}' AND ", namDangKy));

			// Tu ngay -> den ngay.
			if (tuNgay.Year > 1900 && denNgay.Year > 1900)
				query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");

			// Ma don vi.
			if (maDonVi.Length > 0)
				query.Append(string.Format("TK.Ma_DV LIKE '{0}' AND ", maDonVi));
			
			// Tri gia khai bao.
			switch (tgkb_Compare)
			{
				case 0:
					query.Append("TongTGKB >= @TGKB AND ");
					break;
				case 1:
					query.Append("TongTGKB <= @TGKB AND ");
					break;
				case 2:
					query.Append("TongTGKB = @TGKB AND ");
					break;
			}
				
			// Nuoc xuat khau.
			if (nuocXuatKhau != "   ")
				query.Append(string.Format("RTRIM(TK.Nuoc_XK) LIKE '{0}' AND ", nuocXuatKhau));

			// Nuoc nhap khau.
			if (nuocNhapKhau != "   ")
				query.Append(string.Format("RTRIM(TK.Nuoc_NK) LIKE '{0}' AND ", nuocNhapKhau));

			query.Append("'1' = '1' ");
			query.Append(" ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

			SqlParameter[] parameters = new SqlParameter[3];
			parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
			parameters[0].Value = tuNgay;

			parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
			parameters[1].Value = denNgay;

			parameters[2] = new SqlParameter("@TGKB", SqlDbType.Decimal);
			parameters[2].Value = tgkb;

			try
			{
				DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
				foreach (DataRow row in ds.Tables[0].Rows)
				{
					row["TenLoaiHinh"] = FontConverter.TCVN2Unicode(row["TenLoaiHinh"].ToString());
					row["TenHaiQuan"] = FontConverter.TCVN2Unicode(row["TenHaiQuan"].ToString());
				}
				return ds;
			}
			catch (Exception ex)
			{
				throw new Exception(ex.Message + "----" + query);
			}
		}

		//-----------------------------------------------------------------------------------------
		public static SqlDataReader SearchByKey(int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			string connectionString = ConnectionManager.GetConnectionString("SLXNK", maHaiQuan);
			return SearchByKey(connectionString, soToKhai, maLoaiHinh, maHaiQuan, namDangKy);
		}

		//-----------------------------------------------------------------------------------------
		public static SqlDataReader SearchByKey(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuan, int namDangKy)
		{
			StringBuilder query = new StringBuilder(string.Empty);
			query.Append("SELECT ");
			
			query.Append("TK.*, ");
			query.Append("LH.Ten_LH, ");
			query.Append("HQ.Ten_HQ, ");
			query.Append("NXK.Ten_NUOC AS [NuocXK], ");
			query.Append("NNK.Ten_Nuoc AS [NuocNK] ");
			
			query.Append("FROM ");
			
			query.Append("DToKhaiPMD TK ");
			query.Append("INNER JOIN SLHINHPMD LH ON TK.MA_LH = LH.MA_LH ");
			query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");
			query.Append("INNER JOIN SNUOC NXK ON TK.NUOC_XK = NXK.Ma_Nuoc ");
			query.Append("INNER JOIN SNUOC NNK ON TK.Nuoc_NK = NNK.Ma_Nuoc ");
			
			query.Append("WHERE ");
			
			query.Append("TK.SoTK = @SoToKhai AND ");
			query.Append("TK.Ma_LH = @MaLoaiHinh AND ");
			query.Append("TK.Ma_HQ = @MaHaiQuan AND ");
			query.Append("TK.NamDK = @NamDangKy ");


			SqlParameter[] parameters = new SqlParameter[4];
			parameters[0] = new SqlParameter("@SoToKhai", SqlDbType.Int);
			parameters[0].Value = soToKhai;

			parameters[1] = new SqlParameter("@MaLoaiHinh", SqlDbType.Char, 5);
			parameters[1].Value = maLoaiHinh;

			parameters[2] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
			parameters[2].Value = maHaiQuan;

			parameters[3] = new SqlParameter("@NamDangKy", SqlDbType.Int);
			parameters[3].Value = namDangKy;

			return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
		}
        public static DataSet Search2(string connectionString, int soToKhai, string maLoaiHinh, string maHaiQuanMoToKhai, int namDangKy, DateTime tuNgay, DateTime denNgay, string maDonVi, string nuocXuatKhau, string nuocNhapKhau, string maCuaKhau, decimal tgkb, byte tgkb_Compare)
        {
            StringBuilder query = new StringBuilder(string.Empty);
            query.Append("SELECT TK.SoTK AS SoToKhai, ");
            query.Append("TK.Ngay_DK AS NgayDangKy, ");
            query.Append("TK.Ma_LH AS MaLoaiHinh, ");
            query.Append("LH.Ten_LH AS TenLoaiHinh, ");
            query.Append("TK.Ma_HQ AS MaHaiQuan, ");
            query.Append("HQ.Ten_HQ AS TenHaiQuan, ");
            query.Append("TONGTGKB, ");
            query.Append("(CONVERT(varchar(10), TK.SoTK) + '-' + TK.Ma_LH + '-' + RTRIM(TK.MA_HQ) + '-' + CONVERT(varchar(10), TK.NamDK)) AS Khoa ");
            query.Append("FROM ");
            query.Append("DToKhaiPMD TK ");
            query.Append("INNER JOIN SLHINHPMD LH ON TK.MA_LH = LH.MA_LH ");
            query.Append("INNER JOIN SHAIQUAN HQ ON TK.MA_HQ = HQ.MA_HQ ");

            query.Append("WHERE ");
            if (soToKhai > 0)
                query.Append(string.Format("SoTK = {0} AND ", soToKhai));

            // Loai hinh.
            if (maLoaiHinh != "   ")
                query.Append(string.Format("TK.Ma_LH LIKE '{0}' AND ", maLoaiHinh));

            // Hai quan mo to khai.
            if (maHaiQuanMoToKhai != "   ")
                query.Append(string.Format("TK.Ma_HQ LIKE '{0}' AND ", maHaiQuanMoToKhai));

            // Nam dang ky.
            if (namDangKy > 1900)
                query.Append(string.Format("TK.NamDK = '{0}' AND ", namDangKy));

            // Tu ngay -> den ngay.
            if (tuNgay.Year > 1900 && denNgay.Year > 1900)
                query.Append("(TK.Ngay_DK >= @TuNgay AND TK.Ngay_DK <= @DenNgay) AND ");

            // Ma don vi.
            if (maDonVi.Length > 0)
                query.Append(string.Format("TK.Ma_DV LIKE '{0}' AND ", maDonVi));

            // Tri gia khai bao.
            switch (tgkb_Compare)
            {
                case 0:
                    query.Append("TongTGKB >= @TGKB AND ");
                    break;
                case 1:
                    query.Append("TongTGKB <= @TGKB AND ");
                    break;
                case 2:
                    query.Append("TongTGKB = @TGKB AND ");
                    break;
            }

            // Nuoc xuat khau.
            if (nuocXuatKhau != "   ")
                query.Append(string.Format("RTRIM(TK.Nuoc_XK) LIKE '{0}' AND ", nuocXuatKhau));

            // Nuoc nhap khau.
            if (nuocNhapKhau != "   ")
                query.Append(string.Format("RTRIM(TK.Nuoc_NK) LIKE '{0}' AND ", nuocNhapKhau));

            query.Append("'1' = '1' ");
            query.Append(" ORDER BY TK.Ngay_DK DESC, TK.SoTK DESC, TK.Ma_HQ");

            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@TuNgay", SqlDbType.DateTime);
            parameters[0].Value = tuNgay;

            parameters[1] = new SqlParameter("@DenNgay", SqlDbType.DateTime);
            parameters[1].Value = denNgay;

            parameters[2] = new SqlParameter("@TGKB", SqlDbType.Decimal);
            parameters[2].Value = tgkb;

            try
            {
                DataSet ds = SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    row["TenLoaiHinh"] = FontConverter.TCVN2Unicode(row["TenLoaiHinh"].ToString());
                    row["TenHaiQuan"] = FontConverter.TCVN2Unicode(row["TenHaiQuan"].ToString());
                }
                return ds;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message + "----" + query);
            }
        }

	}
}
